package jzqb.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import haha.life.common.BelongToSqlUtils;
import haha.life.common.CutPageBean;
import haha.life.common.SysTool;
import haha.life.normal.CommonConvertUtils;
import haha.life.normal.FileUtil;
import jzqb.bo.*;
import jzqb.common.Result;
import jzqb.dao.AdminLogDao;
import jzqb.dao.KnowledgeDao;
import jzqb.po.VisitPo;
import jzqb.service.KnowledgeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * <p>
 * 服务实现类
 * </p>
 *
 * @author 关注公众号：MarkerHub
 * @since 2020-09-22
 */
@Service
public class KnowledgeServiceImpl implements KnowledgeService {

    @Autowired
    KnowledgeDao knowledgeDao;

    @Autowired
    private AdminLogDao adminLogDao;

    @Override
    public CutPageBean<StandardBo> ComplexSearch(StandardBo standardBo) {
        CutPageBean<StandardBo> cutPageBean = new CutPageBean<>();
        standardBo.setStart((standardBo.getPage() - 1) * standardBo.getPageSize());
        //第一步将 6个参数转换成[] jsonArray 的形式
        //搜索的key
        String searchWord = standardBo.getSearchKeyWord();
        //搜索的value
        String searchValue = standardBo.getSearchValue();
        //搜索的数据来源
        JSONArray dataArr = (JSONArray) JSON.parse(standardBo.getSearchDataResource());
        //搜索的领域来源
        JSONArray areaArr = (JSONArray) JSON.parse(standardBo.getSearchAreaResource());
        //搜索的年份来源
        JSONArray yearArr = (JSONArray) JSON.parse(standardBo.getSearchYearResource());
        //搜索的作者来源
        JSONArray authorArr = (JSONArray) JSON.parse(standardBo.getSearchAuthorResource());
        //搜索的部门来源
        JSONArray deptArr = (JSONArray) JSON.parse(standardBo.getSearchDepartmentResource());
        //groupBy 的key 值
        String groupByKeyWord = standardBo.getGroupByKeyWord();
        // 高级检索的conditionSql
        String conditionSql = standardBo.getConditionSql();
        // 高级检索的 begainDate
        String begainDate = standardBo.getBeginDate();
        // 高级检索的endDate
        String endDate = standardBo.getEndDate();
        // 高级检索的LimitData
        String limitData = standardBo.getLimitData();
        // orderBy value
        String orderByValue = standardBo.getOrderByValue();
        // orderBy sort
        String rderBySort = standardBo.getOrderBySort();
        //拼接前面select * from _______数据来源
        String dataSoure = getPreDataSql(dataArr);
        String afterDataSql = getAfterGroupByDataSql(groupByKeyWord, searchWord, searchValue, yearArr, authorArr, deptArr, conditionSql, begainDate, endDate, limitData);
        //这里是对领域的处理  （领域="*" and 题名="标题") or (题名="标题" not 领域="*")
        String after = "";
        if (areaArr != null && areaArr.size() > 0) {
            String areaBegain = "(";
            String midStr = "";
            String afterStr = " ) or (";
            String titleCon = getTiltileSQL(areaArr);
            if (areaArr != null && areaArr.size() > 0) {
                for (Object o : areaArr) {
                    if (areaArr.size() > 1) {
                        midStr = midStr + "领域= " + o + " OR ";
                    } else {
                        midStr = "领域= " + o;
                    }
                }
            }
            if (areaArr.size() > 1) {
                midStr = midStr.substring(0, midStr.length() - 3);
                midStr = "( " + midStr + ")";
            }
            if (midStr.endsWith("or")) {
                midStr.substring(0, midStr.length() - 2);
            }
            if (!afterDataSql.equals("")) {
                midStr = midStr + " AND ";
                titleCon = " AND " + titleCon;
            }
            if (afterDataSql.startsWith(" AND")) {
                afterDataSql = afterDataSql.substring(4, afterDataSql.length());
            }
            after = areaBegain + midStr + afterDataSql + afterStr + afterDataSql + titleCon + " not 领域 = * " + " ))";
        } else {
            if (afterDataSql.startsWith(" AND")) {
                afterDataSql = afterDataSql.substring(4, afterDataSql.length());
            }
            after = afterDataSql;
        }
        if (orderByValue != null && orderByValue != "") {
            after = after + " order by " + orderByValue;
        } else {
            if(dataSoure.contains(SysTool.QBBK_METADATA)){
                KnowleageConditionBo knowleageConditionBos = new KnowleageConditionBo();
                knowleageConditionBos.setAfeterSql(after);
                Long count = knowledgeDao.findTheValue(knowleageConditionBos);
                if (count == 0) {
                    //todo
                } else {
                    after = after + " order by  状态 DESC ";
                }
            }
        }
        if (rderBySort != null && rderBySort != "") {
            after = after + " " + rderBySort;
        }
        KnowleageConditionBo knowleageConditionBo = new KnowleageConditionBo();
        knowleageConditionBo.setDataSql(dataSoure);
        knowleageConditionBo.setAfeterSql(after);
        knowleageConditionBo.setPage(standardBo.getPage());
        knowleageConditionBo.setStart(standardBo.getStart());
        knowleageConditionBo.setPageSize(standardBo.getPageSize());
        List<StandardBo> standardBos = knowledgeDao.ComplexSearch(knowleageConditionBo);
        if (standardBos == null || standardBos.size() == 0) {
            cutPageBean.initCutPage(0, standardBo.getPageSize(), standardBos);
            return cutPageBean;
        }
        String getBrowseID = "";
        // 名称转化
        for (StandardBo bo : standardBos) {
            String area = bo.getField();
            bo.setAreaShowName(area);
            Integer sysID = bo.getSysID();
            String overallID = "";
            if (!"".equals(sysID) && sysID != null && sysID != 0) {//判断唯一值是用表名+ID还是表名+文件名
                overallID = bo.getTableName() + "_" + sysID;
            } else {
                overallID = bo.getTableName() + "_" + bo.getFileName();
            }
            bo.setOverallID(overallID);
            getBrowseID += overallID + ",";
            // 对下载频次进行处理
            if (bo.getDownloadTimes() == null || bo.getDownloadTimes().equals("")) {
                bo.setDownloadTimes("0");
            }
        }
        List<VisitPo> browseList = adminLogDao.getBrowseByOverallID(getBrowseID);
        HashMap<String, Integer> browseMap = new HashMap<>();
        for (VisitPo visitPo : browseList) {
            browseMap.put(visitPo.getOverallid(), visitPo.getBrowse());
        }
        for (StandardBo bo : standardBos) {
            String overallID = bo.getOverallID();
            Integer integer = browseMap.get(overallID);
            if (integer == null || "".equals(integer)) integer = 0;
            bo.setBrowseFrequency(integer.toString());
        }
        Long count = knowledgeDao.findComplexSearchCount(knowleageConditionBo);
        cutPageBean.initCutPage(count.intValue(), standardBo.getPageSize(), standardBos);
        return cutPageBean;
    }

    @Override
    public Result ComplexSearchGroup(StandardBo standardBo) {
        CutPageBean<StandardBo> cutPageBean = new CutPageBean<>();
        standardBo.setStart((standardBo.getPage() - 1) * standardBo.getPageSize());
        //第一步将 6个参数转换成[] jsonArray 的形式
        //搜索的key
        String searchWord = standardBo.getSearchKeyWord();
        //搜索的value
        String searchValue = standardBo.getSearchValue();
        //搜索的数据来源
        JSONArray dataArr = (JSONArray) JSON.parse(standardBo.getSearchDataResource());
        //搜索的领域来源
        JSONArray areaArr = (JSONArray) JSON.parse(standardBo.getSearchAreaResource());
        //搜索的年份来源
        JSONArray yearArr = (JSONArray) JSON.parse(standardBo.getSearchYearResource());
        //搜索的作者来源
        JSONArray authorArr = (JSONArray) JSON.parse(standardBo.getSearchAuthorResource());
        //搜索的部门来源
        JSONArray deptArr = (JSONArray) JSON.parse(standardBo.getSearchDepartmentResource());
        //groupBy 的key 值
        String groupByKeyWord = standardBo.getGroupByKeyWord();
        // 高级检索的conditionSql
        String conditionSql = standardBo.getConditionSql();
        // 高级检索的 begainDate
        String begainDate = standardBo.getBeginDate();
        // 高级检索的endDate
        String endDate = standardBo.getEndDate();
        // 高级检索的LimitData
        String limitData = standardBo.getLimitData();
        // orderBy value
        String orderByValue = standardBo.getOrderByValue();
        // orderBy sort
        String rderBySort = standardBo.getOrderBySort();
        //拼接前面select * from _______数据来源
        String preSql = getPresql(groupByKeyWord);
        //拼接前面select * from _______数据来源
        String dataSourceSql = getPreDataSql(dataArr);
        String afterDataSql = getAfterGroupByDataSql(groupByKeyWord, searchWord, searchValue, yearArr, authorArr, deptArr, conditionSql, begainDate, endDate, limitData);
        //这里是对领域的处理  （领域="*" and 题名="标题") or (题名="标题" not 领域="*")
        String after = "";
        String groupByWord = "";
        String afterDataSqlResult = "";
        if (areaArr != null && areaArr.size() > 0) {
            String areaBegain = "(";
            String midStr = "";
            String afterStr = " ) or (";
            String titleCon = getTiltileSQL(areaArr);
            if (areaArr != null && areaArr.size() > 0) {
                for (Object o : areaArr) {
                    if (areaArr.size() > 1) {
                        midStr = midStr + "领域= " + o + " OR ";
                    } else {
                        midStr = "领域= " + o;
                    }
                }
            }
            if (areaArr.size() > 1) {
                midStr = midStr.substring(0, midStr.length() - 3);
                midStr = "( " + midStr + ")";
            }
            if (midStr.endsWith("or")) {
                midStr.substring(0, midStr.length() - 2);
            }
            if (!afterDataSql.equals("")) {
                midStr = midStr + " AND ";
                titleCon = " AND " + titleCon;
            }
            if (afterDataSql.startsWith(" AND")) {
                afterDataSql = afterDataSql.substring(4, afterDataSql.length());
            }
            afterDataSqlResult = areaBegain + midStr + afterDataSql + afterStr + afterDataSql + titleCon + " not 领域 = * " + " ))";
            // 对gropBy的字段进行整理
            if (groupByKeyWord != null) {
                groupByWord = " " + "group by " + groupByKeyWord + " order by count(*)  desc  limit " + SysTool.LIMIITNUM;
            }
            after = areaBegain + midStr + afterDataSql + afterStr + afterDataSql + titleCon + " not 领域 = * " + " ))" + groupByWord;
        } else {
            // 对gropBy的字段进行整理
            if (groupByKeyWord != null) {
                groupByWord = " " + "group by " + groupByKeyWord + " order by count(*)  desc  limit " + SysTool.LIMIITNUM;
            }
            if (afterDataSql.startsWith(" AND")) {
                afterDataSql = afterDataSql.substring(4, afterDataSql.length());
            }
            after = afterDataSql + groupByWord;
            afterDataSqlResult = afterDataSql;
        }
        if (orderByValue != null && orderByValue != "") {
            after = after + "order by " + orderByValue;
        }
        if (rderBySort != null && rderBySort != "") {
            after = after + " " + rderBySort;
        }
        KnowleageConditionBo knowleageConditionBo = new KnowleageConditionBo();
        knowleageConditionBo.setPreSql(preSql);
        knowleageConditionBo.setDataSql(dataSourceSql);
        knowleageConditionBo.setAfeterSql(after);
        List<StandKnowleageBo> standKnowleageBo = knowledgeDao.ComplexSearchGroup(knowleageConditionBo);
        if (standKnowleageBo == null || standKnowleageBo.size() == 0) {
            if (groupByKeyWord.equals("年")) {
                standKnowleageBo = new ArrayList<StandKnowleageBo>();
                if (yearArr != null && yearArr.size() > 0) {
                    for (Object o : yearArr) {
                        StandKnowleageBo standKnowleageBo1 = new StandKnowleageBo();
                        standKnowleageBo1.setKey(o.toString());
                        standKnowleageBo1.setValue("0");
                        standKnowleageBo.add(standKnowleageBo1);
                    }
                }
            } else if (groupByKeyWord.equals("文献来源")) {
                standKnowleageBo = new ArrayList<StandKnowleageBo>();
                if (deptArr != null && deptArr.size() > 0) {
                    for (Object o : deptArr) {
                        StandKnowleageBo standKnowleageBo1 = new StandKnowleageBo();
                        standKnowleageBo1.setKey(o.toString());
                        standKnowleageBo1.setValue("0");
                        standKnowleageBo.add(standKnowleageBo1);
                    }
                }
            } else if (groupByKeyWord.equals("作者")) {
                standKnowleageBo = new ArrayList<StandKnowleageBo>();
                if (authorArr != null && authorArr.size() > 0) {
                    for (Object o : authorArr) {
                        StandKnowleageBo standKnowleageBo1 = new StandKnowleageBo();
                        standKnowleageBo1.setKey(o.toString());
                        standKnowleageBo1.setValue("0");
                        standKnowleageBo.add(standKnowleageBo1);
                    }
                }
            }
        }
        //这里进行list 排重
        Set<StandKnowleageBo> standKnowleageBos = new HashSet<>(standKnowleageBo);
        List<StandKnowleageBo> standKnowleageBostemp = new ArrayList<>(standKnowleageBos);
        List<StandKnowleageBo> standKnowleageBosResult = new ArrayList<>();
        for (int x = 0; x < standKnowleageBostemp.size(); x++) {
            standKnowleageBosResult.add(standKnowleageBostemp.get(x));
        }
        Collections.sort(standKnowleageBosResult); // 按年龄排序
        if (standKnowleageBosResult.size() > 5) {
            standKnowleageBosResult = standKnowleageBosResult.subList(0, 5);
        }
        // 然后从list 中选取前5个
        // 这里在对有 ;的 名字进行特殊处理
        for (StandKnowleageBo knowleageBo : standKnowleageBosResult) {
            String[] arr = knowleageBo.getKey().replaceAll(",", ";").split(";");
            Integer result = Integer.parseInt(knowleageBo.getValue());
            if (arr.length > 1) {
                for (String str : arr) {
                    CountNumberBo countNumberBo = new CountNumberBo();
                    countNumberBo.setCountStr(str);
                    countNumberBo.setDataSource(dataSourceSql);
                    countNumberBo.setAfterDataSql(afterDataSqlResult);
                    Long countNumber = knowledgeDao.selectCountNumber(countNumberBo);
                    Integer temp = countNumber.intValue();
                    Integer keyValue = Integer.parseInt(knowleageBo.getValue());
                    Integer resultTemp = Math.abs(temp - keyValue);
                    if (result >= resultTemp) {
                        knowleageBo.setKey(str);
                        result = resultTemp;
                    }
                }
            }
        }
        return Result.succ(standKnowleageBosResult);
    }


    private String getTiltileSQL(JSONArray areaArr) {
        if (areaArr != null && areaArr.size() > 0) {
            String deMainRes = FileUtil.readJsonFile("domain.json");
            JSONArray dataArr = (JSONArray) JSON.parse(deMainRes);
            List<String> arr = new ArrayList<>();
            List<DomainBo> domainBos = dataArr.toJavaList(DomainBo.class);
            for (Object obj : areaArr) {
                String res = (String) obj;
                for (DomainBo domainBo : domainBos) {
                    if (domainBo.getDbCode().equals(res)) {
                        String[] domainArr = domainBo.getKeyWord();
                        for (String s : domainArr) {
                            arr.add(s);
                        }
                    }
                }
            }
            String[] params = CommonConvertUtils.ArrToStringArr(arr);
            String titleBelongTo = BelongToSqlUtils.getBelongToSql(params, "关键词");
            return "(" + titleBelongTo;
        } else {
            return "(";
        }
    }

    /**
     * 数据来源统计数量
     *
     * @return
     */
    @Override
    public Result ComplexDataSurceGroup() {
        //查询每个表的数据统计数量
        List<StandKnowleageBo> standKnowleageBos = new ArrayList<>();
        StandKnowleageBo JZQBZS_METADATA = new StandKnowleageBo(SysTool.getDBBoByKey(SysTool.QBZYUAN).getDbCode());
        StandKnowleageBo JZCG_METADATA = new StandKnowleageBo(SysTool.getDBBoByKey(SysTool.YJCG).getDbCode());
        StandKnowleageBo JZQBBK_METADATA = new StandKnowleageBo(SysTool.getDBBoByKey(SysTool.BKCT).getDbCode());
        StandKnowleageBo YQTZTWX_METADATA = new StandKnowleageBo(SysTool.getDBBoByKey(SysTool.ZTWX).getDbCode());
        StandKnowleageBo JZJSYY_METADATA = new StandKnowleageBo(SysTool.getDBBoByKey(SysTool.HYSP).getDbCode());
        StandKnowleageBo CCNDLAST2019_06 = new StandKnowleageBo(SysTool.getDBBoByKey(SysTool.BZ).getDbCode());
        StandKnowleageBo CDFDLAST2019_06 = new StandKnowleageBo(SysTool.getDBBoByKey(SysTool.BSLW).getDbCode());
        StandKnowleageBo CJFDLAST2019_06 = new StandKnowleageBo(SysTool.getDBBoByKey(SysTool.QK).getDbCode());
        StandKnowleageBo CMFDLAST2019_06 = new StandKnowleageBo(SysTool.getDBBoByKey(SysTool.SSLW).getDbCode());
        StandKnowleageBo CPFDLAST2019_06 = new StandKnowleageBo(SysTool.getDBBoByKey(SysTool.ZGHY).getDbCode());
        StandKnowleageBo IPFDLAST2019_06 = new StandKnowleageBo(SysTool.getDBBoByKey(SysTool.GJHY).getDbCode());
        standKnowleageBos.add(JZQBZS_METADATA);
        standKnowleageBos.add(JZCG_METADATA);
        standKnowleageBos.add(JZQBBK_METADATA);
        standKnowleageBos.add(CCNDLAST2019_06);
        standKnowleageBos.add(CDFDLAST2019_06);
        standKnowleageBos.add(CJFDLAST2019_06);
        standKnowleageBos.add(CMFDLAST2019_06);
        standKnowleageBos.add(CPFDLAST2019_06);
        standKnowleageBos.add(IPFDLAST2019_06);
        standKnowleageBos.add(YQTZTWX_METADATA);
        standKnowleageBos.add(JZJSYY_METADATA);
        for (StandKnowleageBo standKnowleageBo : standKnowleageBos) {
            DataCountBo dataCountBo = new DataCountBo();
            dataCountBo.setDataSource(standKnowleageBo.getKey());
            if (SysTool.getDBBoByKey(SysTool.BKCT).getDbCode().equals(standKnowleageBo.getKey())) {
                dataCountBo.setStatueSql("状态 = 2 ");
            }
            Integer number = knowledgeDao.getDataSourCount(dataCountBo);
            if (number != null) {
                standKnowleageBo.setValue(number.toString());
            } else {
                standKnowleageBo.setValue("0");
            }
        }
        return Result.succ(standKnowleageBos);
    }

    @Override
    public Result getKnowledgeDetail(QBZSBo qbzsBo) {
        QBZSBo qbzs = knowledgeDao.getKnowledgeDetail(qbzsBo);
        return Result.succ(qbzs);
    }

    @Override
    public StandardBo getLocalKnowLeage(QBZSBo qbzsBo) {
        StandardBo qbzs = knowledgeDao.getLocalKnowLeage(qbzsBo);
        return qbzs;
    }

    @Override
    public List<StandardBo> ralatedArticlesList(String sql) {
        return knowledgeDao.ralatedArticlesList(sql);
    }

    @Override
    public StandardBo getBoById(QBZSBo qbzsBo) {
        return knowledgeDao.getBoById(qbzsBo);
    }

    @Override
    public Boolean updateDownTime(StandardBo standardBo) {
        return knowledgeDao.updateDownTime(standardBo);
    }

    @Override
    public List<StandardBo> getResultByAuthor(StandardBo standardBo) {
        return knowledgeDao.getResultByAuthor(standardBo);
    }

    @Override
    public List<StandardBo> getResultByOrganization(StandardBo standardBo) {
        return knowledgeDao.getResultByOrganization(standardBo);
    }

    @Override
    public List<StandardBo> getResultByKeyword(StandardBo standardBo) {
        return knowledgeDao.getResultByKeyword(standardBo);
    }

    @Override
    public List<StandardBo> getRalatedArticlesList(StandardBo standardBo) {
        return knowledgeDao.getRalatedArticlesList(standardBo);
    }


    private String getPresql(String groupByKeyWord) {
        //count(*) as value,年 as key
        return "count(*) as value, " + groupByKeyWord + " as key";
    }

    private String getAfterGroupByDataSql(String groupByKeyWord, String searchWord, String searchValue, JSONArray yearArr, JSONArray authorArr, JSONArray deptArr, String conditionSql, String begainDate, String endDate, String LimitData) {
        StringBuffer stringBuffer = new StringBuffer();
        String yearBelongTo = null;
        String authorBelongTo = null;
        String deptBelongTo = null;
        String groupByWord = null;
        stringBuffer.append("((题名=* not 状态=*) or 状态=2 ) ");
        // 这里对主题做特殊处理
        if (searchWord != null && searchWord != null && searchValue != "" && searchValue != "") {
            if (searchWord.equals("主题")) {
                stringBuffer.append(" ").append("AND ").append(searchWord).append(" % ").append("\"").append(searchValue).append("\"");
            } else if (searchWord.equals("题名")) {
                stringBuffer.append(" ").append("AND ").append(searchWord).append(" % ").append("\"").append(searchValue).append("\"");
            } else if (searchWord.equals("作者")) {
                stringBuffer.append(" ").append("AND ").append(searchWord).append(" % ").append("\"").append("*").append(searchValue).append("\"");
            } else if (searchWord.equals("关键词")) {
                stringBuffer.append(" ").append("AND ").append(searchWord).append(" % ").append("\"").append("*").append(searchValue).append("\"");
            } else if (searchWord.equals("文献来源")) {
                stringBuffer.append(" ").append("AND ").append(searchWord).append(" % ").append("\"").append("*").append(searchValue).append("\"");
            } else {
                stringBuffer.append(" ").append("AND ").append(searchWord).append(" = ").append("\"").append("*").append(searchValue).append("\"");
            }
        }
        if (conditionSql != null && conditionSql != "") {
            stringBuffer.append("  and  ").append(conditionSql).append(" ");
        }
        //begainDate
        if (begainDate != null && begainDate != "") {
            String res = " AND 发表时间 > " + "\"" + begainDate + "\"";
            stringBuffer.append(res).append(" ");
        }
        //endDate
        if (endDate != null && endDate != "") {
            String res = " AND 发表时间 < " + "\"" + endDate + "\"";
            stringBuffer.append(res).append(" ");
        }
        //LimitData
        if (LimitData != null && LimitData != "") {
            long mill = System.currentTimeMillis();
            if ("最近一周".equals(LimitData)) {
                mill -= 86400000l * 7;
            } else if ("最近一月".equals(LimitData)) {
                mill -= 86400000l * 31;
            } else if ("最近半年".equals(LimitData)) {
                mill -= 86400000l * 30.5 * 6;
            } else if ("最近一年".equals(LimitData)) {
                mill -= 86400000l * 365;
            }
            // 不限为null
            String dataStr = new SimpleDateFormat("yyyy-MM-dd").format(mill);
            if (dataStr != null && dataStr != "") {
                String res = " AND  发表时间 > " + "\"" + dataStr + "\"";
                stringBuffer.append(res).append(" ");
            }
        }
        // 这里是对年限的处理
        if (yearArr != null && yearArr.size() > 0) {
            String[] years = CommonConvertUtils.JsonArrToStringArr(yearArr);
            String yearTemp = getYearSql(years);
            //       String yearTemp = BelongToSqlUtils.getBelongToSql(years, "年");
            yearBelongTo = " AND " + yearTemp;
        }
        // 这里是对作者的处理
        if (authorArr != null && authorArr.size() > 0) {
            String[] authors = CommonConvertUtils.JsonArrToStringArr(authorArr);
            String authorTemp = getAuthorSql(authors);
            //String authorTemp = BelongToSqlUtils.getBelongToSql(authors, "作者");
            authorBelongTo = " AND " + authorTemp;
        }
        // 这里是对部门的处理
        if (deptArr != null && deptArr.size() > 0) {
//            String[] depts = CommonConvertUtils.JsonArrToStringArr(deptArr);
//            String authorTemp = getDeptSql(deptArr);
            //TODO  这里已经处理了 非要相同的话 只能5个都在查询一边了.
            String[] depts = CommonConvertUtils.JsonArrToStringArr(deptArr);
            String deptTemp = getDeptSql(depts);
            deptBelongTo = " AND " + deptTemp;
        }

        if (yearBelongTo != null) {
            stringBuffer.append(yearBelongTo);
        }
        if (authorBelongTo != null) {
            stringBuffer.append(authorBelongTo);
        }
        if (deptBelongTo != null) {
            stringBuffer.append(deptBelongTo);
        }
        return stringBuffer.toString();
    }


    private String getDeptSql(String[] depts) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("(");
        ArrayList<String> authorList = new ArrayList<>();
        for (String author : depts) {
            if (author.contains(";")) {
                String[] temp = author.split(";");
                stringBuffer.append("(");
                for (String string : temp) {
                    stringBuffer.append(" 文献来源 % ").append("\"").append(string).append("\"").append(" and");
                }
                if (stringBuffer.toString().endsWith("and")) {
                    String string = stringBuffer.substring(0, stringBuffer.length() - 3);
                    stringBuffer = new StringBuffer(string);
                }
                stringBuffer.append(") or");
            } else {
                stringBuffer.append(" 文献来源 % ").append("\"").append(author).append("\"").append(" or");
            }
        }
        if (stringBuffer.toString().endsWith("or")) {
            String string = stringBuffer.substring(0, stringBuffer.length() - 2);
            stringBuffer = new StringBuffer(string);
        }
        stringBuffer.append(")");
        return stringBuffer.toString();
    }

    private String getAuthorSql(String[] authors) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("(");
        ArrayList<String> authorList = new ArrayList<>();
        for (String author : authors) {
            if (author.contains(";")) {
                String[] temp = author.split(";");
                stringBuffer.append("(");
                for (String string : temp) {
                    stringBuffer.append(" 作者 % ").append("\"").append(string).append("\"").append(" and");
                }
                if (stringBuffer.toString().endsWith("and")) {
                    String string = stringBuffer.substring(0, stringBuffer.length() - 3);
                    stringBuffer = new StringBuffer(string);
                }
                stringBuffer.append(") or");
            } else {
                stringBuffer.append(" 作者 % ").append("\"").append(author).append("\"").append(" or");
            }
        }
        if (stringBuffer.toString().endsWith("or")) {
            String string = stringBuffer.substring(0, stringBuffer.length() - 2);
            stringBuffer = new StringBuffer(string);
        }
        stringBuffer.append(")");
        return stringBuffer.toString();
    }

    private String getYearSql(String[] years) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("(");

        for (String year : years) {
            stringBuffer.append(" 年 =").append("\"").append(year).append("\"").append(" or");
        }
        if (stringBuffer.toString().endsWith("or")) {
            String string = stringBuffer.substring(0, stringBuffer.length() - 2);
            stringBuffer = new StringBuffer(string);
        }
        stringBuffer.append(")");
        return stringBuffer.toString();
    }

    private String getPreDataSql(JSONArray dataArr) {
            StringBuffer stringBuffer = new StringBuffer();
            //这里循环1次 保证 我们的QBBK 在最前面。
            String QBBK = "";
            for (Object o : dataArr) {
                if (o.toString().equals(SysTool.QBBK_METADATA)) {
                    QBBK = SysTool.QBBK_METADATA + ",";
                } else {
                    stringBuffer = stringBuffer.append(o).append(",");
                }
            }
            String string = QBBK + stringBuffer;
            return (string).substring(0, string.length() - 1).toString();
        }
}